package com.jichaoyun.service.impl;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.druid.support.json.JSONUtils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jichaoyun.common.enums.MyExceptiontType;
import com.jichaoyun.common.ex.MyException;
import com.jichaoyun.common.model.Result;
import com.jichaoyun.common.utils.ExcelStyleUtils;
import com.jichaoyun.common.utils.PoiExcelStyle;
import com.jichaoyun.dao.app.mapper.AppUserMapper;
import com.jichaoyun.dao.mapper.*;
import com.jichaoyun.model.app.AppUser;
import com.jichaoyun.model.entity.*;
import com.jichaoyun.model.vo.*;
import com.jichaoyun.service.IStationInfoService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jichaoyun.service.util.RedisUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author zhangzhen
 * @since 2023-11-11
 */
@Service
public class StationInfoServiceImpl extends ServiceImpl<StationInfoMapper, StationInfo> implements IStationInfoService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private AppUserMapper appUserMapper;

    @Autowired
    private StationInfoMapper stationInfoMapper;

    @Autowired
    private LineInfoMapper lineInfoMapper;

    @Autowired
    private FlowHourMapper flowHourMapper;
    @Autowired
    private RealdataCacheMapper realdataCacheMapper;

    @Autowired
    private RedisUtil redisUtil;

    @Override
    public List<Map<String, Object>> getAppStationNameList(Integer id) {
        AppUser user = this.appUserMapper.selectById(id);
        List<Map<String,Object>> res = new ArrayList<>();
        if(user==null) throw new MyException(MyExceptiontType.USERNOTFIND,"用户不存在或者已经删除，请更换用户");
        if(user.getUserRole()==null) throw new MyException(MyExceptiontType.USERNOTFIND,"用户角色未设置，请联系管理员设置角色！");
        if(user.getUserRole().equals(0)){
            //普通用户只能看到自己的站点
            LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(StationInfo::getAppUserId,id)
                    .eq(StationInfo::getIsUse,1);
            List<StationInfo> result = this.baseMapper.selectList(wrapper);
            for(StationInfo item:result){
                HashMap<String, Object> map = new HashMap<>();
                map.put("station_no",item.getStationNo());
                map.put("station_name",item.getStationName());
                res.add(map);
            }
        }else {
            QueryWrapper<StationInfo> wrapper = new QueryWrapper<>();
            wrapper.select("station_no,station_name")
                    .eq("is_use",1);
            List<StationInfo> station_list = stationInfoMapper.selectList(wrapper);
            for(StationInfo item : station_list){
                HashMap<String, Object> map = new HashMap<>();
                map.put("station_no",item.getStationNo());
                map.put("station_name",item.getStationName());
                res.add(map);
            }
        }
        return res;
    }

    @Override
    public List<Map<String, Object>> getFiveFlowHigh() {
        List<Map<String, Object>> res = new ArrayList<>();

        LambdaQueryWrapper<RealdataCache> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(RealdataCache::getRealId);
        List<RealdataCache> realdataCacheList = realdataCacheMapper.selectList(queryWrapper);
        RealdataCache realdataCache = realdataCacheList.get(0);
        LocalDateTime dateTime = realdataCache.getRecordTime();

        LambdaQueryWrapper<StationInfo> queryWrapper1 = new LambdaQueryWrapper<>();
        queryWrapper1.eq(StationInfo::getLineId,24)
                .eq(StationInfo::getUserType,0)
                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList = stationInfoMapper.selectList(queryWrapper1);

        List<Integer> stationNoList = new ArrayList<>();
        for(StationInfo stationInfo:stationInfoList){
            stationNoList.add(stationInfo.getStationNo());
        }


        LambdaQueryWrapper<StationInfo> queryWrapper11 = new LambdaQueryWrapper<>();
        queryWrapper11.eq(StationInfo::getLineId,24)
                .eq(StationInfo::getUserType,1)
                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList1 = stationInfoMapper.selectList(queryWrapper11);

        List<Integer> stationNoList1 = new ArrayList<>();
        for(StationInfo stationInfo1:stationInfoList1){
            stationNoList1.add(stationInfo1.getStationNo());
        }

        LocalDateTime firstTime = dateTime.minusMinutes(6);
        while (firstTime.isBefore(dateTime)){
            Map<String, Object> map = new HashMap<>();
            float total = 0f;
            float part = 0f;
            if (!stationNoList.isEmpty()){
                LambdaQueryWrapper<RealdataCache> queryWrapper2 = new LambdaQueryWrapper<>();
                queryWrapper2.ge(RealdataCache::getRecordTime,firstTime)
                        .le(RealdataCache::getRecordTime,firstTime.plusMinutes(1))
                        .in(RealdataCache::getStation,stationNoList);
                List<RealdataCache> realdataCaches = realdataCacheMapper.selectList(queryWrapper2);

                // 创建一个Map来存储每个站台的最新记录
                Map<Integer, RealdataCache> latestRecordsByStation = new HashMap<>();
                for (RealdataCache dataCache : realdataCaches) {
                    Integer stationNo = dataCache.getStation();
                    if (!latestRecordsByStation.containsKey(stationNo) ||
                            dataCache.getRecordTime().isAfter(latestRecordsByStation.get(stationNo).getRecordTime())) {
                        latestRecordsByStation.put(stationNo, dataCache);
                    }
                }

                // 从Map中提取最新的记录列表
                List<RealdataCache> filteredRealdataCaches = new ArrayList<>(latestRecordsByStation.values());
                for(RealdataCache realdataCache1: filteredRealdataCaches){
                    if(realdataCache1.getFlow() != null){
                        total+=realdataCache1.getFlow();
                    }
                }

            }

            if (!stationNoList1.isEmpty()){
                LambdaQueryWrapper<RealdataCache> queryWrapper22 = new LambdaQueryWrapper<>();
                queryWrapper22.ge(RealdataCache::getRecordTime,firstTime)
                        .le(RealdataCache::getRecordTime,firstTime.plusMinutes(1))
                        .in(RealdataCache::getStation,stationNoList1);
                List<RealdataCache> realdataCaches2 = realdataCacheMapper.selectList(queryWrapper22);

                // 创建一个Map来存储每个站台的最新记录
                Map<Integer, RealdataCache> latestRecordsByStation2 = new HashMap<>();
                for (RealdataCache dataCache2 : realdataCaches2) {
                    Integer stationNo = dataCache2.getStation();
                    if (!latestRecordsByStation2.containsKey(stationNo) ||
                            dataCache2.getRecordTime().isAfter(latestRecordsByStation2.get(stationNo).getRecordTime())) {
                        latestRecordsByStation2.put(stationNo, dataCache2);
                    }
                }

                // 从Map中提取最新的记录列表
                List<RealdataCache> filteredRealdataCaches2 = new ArrayList<>(latestRecordsByStation2.values());

                for(RealdataCache realdataCache2: filteredRealdataCaches2){
                    if(realdataCache2.getFlow() != null){
                        part+=realdataCache2.getFlow();
                    }
                }
            }

            map.put("时间",firstTime.toString());
            map.put("总表",total);
            map.put("分表",part);

            res.add(map);
            firstTime = firstTime.plusMinutes(1);
        }
        return res;
    }



    @Override
    public List<Map<String, Object>> getFiveFlowLow() {
        List<Map<String, Object>> res = new ArrayList<>();

        LambdaQueryWrapper<RealdataCache> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(RealdataCache::getRealId);
        List<RealdataCache> realdataCacheList = realdataCacheMapper.selectList(queryWrapper);
        RealdataCache realdataCache = realdataCacheList.get(0);
        LocalDateTime dateTime = realdataCache.getRecordTime();

        LambdaQueryWrapper<StationInfo> queryWrapper1 = new LambdaQueryWrapper<>();
        queryWrapper1.eq(StationInfo::getLineId,25)
                .eq(StationInfo::getUserType,0)
                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList = stationInfoMapper.selectList(queryWrapper1);

        List<Integer> stationNoList = new ArrayList<>();
        for(StationInfo stationInfo:stationInfoList){
            stationNoList.add(stationInfo.getStationNo());
        }


        LambdaQueryWrapper<StationInfo> queryWrapper11 = new LambdaQueryWrapper<>();
        queryWrapper11.eq(StationInfo::getLineId,25)
                .eq(StationInfo::getUserType,1)
                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList1 = stationInfoMapper.selectList(queryWrapper11);

        List<Integer> stationNoList1 = new ArrayList<>();
        for(StationInfo stationInfo1:stationInfoList1){
            stationNoList1.add(stationInfo1.getStationNo());
        }

        LocalDateTime firstTime = dateTime.minusMinutes(6);
        while (firstTime.isBefore(dateTime)){
            Map<String, Object> map = new HashMap<>();
            float total = 0f;
            float part = 0f;
            if (!stationNoList.isEmpty()){
                LambdaQueryWrapper<RealdataCache> queryWrapper2 = new LambdaQueryWrapper<>();
                queryWrapper2.ge(RealdataCache::getRecordTime,firstTime)
                        .le(RealdataCache::getRecordTime,firstTime.plusMinutes(1))
                        .in(RealdataCache::getStation,stationNoList);
                List<RealdataCache> realdataCaches = realdataCacheMapper.selectList(queryWrapper2);

                // 创建一个Map来存储每个站台的最新记录
                Map<Integer, RealdataCache> latestRecordsByStation = new HashMap<>();
                for (RealdataCache dataCache : realdataCaches) {
                    Integer stationNo = dataCache.getStation();
                    if (!latestRecordsByStation.containsKey(stationNo) ||
                            dataCache.getRecordTime().isAfter(latestRecordsByStation.get(stationNo).getRecordTime())) {
                        latestRecordsByStation.put(stationNo, dataCache);
                    }
                }

                // 从Map中提取最新的记录列表
                List<RealdataCache> filteredRealdataCaches = new ArrayList<>(latestRecordsByStation.values());
                for(RealdataCache realdataCache1: filteredRealdataCaches){
                    if(realdataCache1.getFlow() != null){
                        total+=realdataCache1.getFlow();
                    }
                }

            }

            if (!stationNoList1.isEmpty()){
                LambdaQueryWrapper<RealdataCache> queryWrapper22 = new LambdaQueryWrapper<>();
                queryWrapper22.ge(RealdataCache::getRecordTime,firstTime)
                        .le(RealdataCache::getRecordTime,firstTime.plusMinutes(1))
                        .in(RealdataCache::getStation,stationNoList1);
                List<RealdataCache> realdataCaches2 = realdataCacheMapper.selectList(queryWrapper22);

                // 创建一个Map来存储每个站台的最新记录
                Map<Integer, RealdataCache> latestRecordsByStation2 = new HashMap<>();
                for (RealdataCache dataCache2 : realdataCaches2) {
                    Integer stationNo = dataCache2.getStation();
                    if (!latestRecordsByStation2.containsKey(stationNo) ||
                            dataCache2.getRecordTime().isAfter(latestRecordsByStation2.get(stationNo).getRecordTime())) {
                        latestRecordsByStation2.put(stationNo, dataCache2);
                    }
                }

                // 从Map中提取最新的记录列表
                List<RealdataCache> filteredRealdataCaches2 = new ArrayList<>(latestRecordsByStation2.values());

                for(RealdataCache realdataCache2: filteredRealdataCaches2){
                    if(realdataCache2.getFlow() != null){
                        part+=realdataCache2.getFlow();
                    }
                }
            }

            map.put("时间",firstTime.toString());
            map.put("总表",total);
            map.put("分表",part);

            res.add(map);
            firstTime = firstTime.plusMinutes(1);
        }
        return res;
    }



    @Override
    public List<Map<String, Object>> getTenFlow() {
        List<Map<String, Object>> res = new ArrayList<>();

        LambdaQueryWrapper<RealdataCache> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(RealdataCache::getRealId);
        List<RealdataCache> realdataCacheList = realdataCacheMapper.selectList(queryWrapper);
        RealdataCache realdataCache = realdataCacheList.get(0);
        LocalDateTime dateTime = realdataCache.getRecordTime();

        LambdaQueryWrapper<StationInfo> queryWrapper1 = new LambdaQueryWrapper<>();
        queryWrapper1.ne(StationInfo::getLineId,23).eq(StationInfo::getUserType,0)
                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList = stationInfoMapper.selectList(queryWrapper1);

        List<Integer> stationNoList = new ArrayList<>();
        for(StationInfo stationInfo:stationInfoList){
            stationNoList.add(stationInfo.getStationNo());
        }


        LambdaQueryWrapper<StationInfo> queryWrapper11 = new LambdaQueryWrapper<>();
        queryWrapper11.ne(StationInfo::getLineId,23).eq(StationInfo::getUserType,1)
                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList1 = stationInfoMapper.selectList(queryWrapper11);

        List<Integer> stationNoList1 = new ArrayList<>();
        for(StationInfo stationInfo1:stationInfoList1){
            stationNoList1.add(stationInfo1.getStationNo());
        }

        LocalDateTime firstTime = dateTime.minusMinutes(11);
        while (firstTime.isBefore(dateTime)){
            Map<String, Object> map = new HashMap<>();
            float total = 0f;
            float part = 0f;
            if (!stationNoList.isEmpty()){
                LambdaQueryWrapper<RealdataCache> queryWrapper2 = new LambdaQueryWrapper<>();
                queryWrapper2.ge(RealdataCache::getRecordTime,firstTime)
                        .le(RealdataCache::getRecordTime,firstTime.plusMinutes(1))
                        .in(RealdataCache::getStation,stationNoList);
                List<RealdataCache> realdataCaches = realdataCacheMapper.selectList(queryWrapper2);

                // 创建一个Map来存储每个站台的最新记录
                Map<Integer, RealdataCache> latestRecordsByStation = new HashMap<>();
                for (RealdataCache dataCache : realdataCaches) {
                    Integer stationNo = dataCache.getStation();
                    if (!latestRecordsByStation.containsKey(stationNo) ||
                            dataCache.getRecordTime().isAfter(latestRecordsByStation.get(stationNo).getRecordTime())) {
                        latestRecordsByStation.put(stationNo, dataCache);
                    }
                }

                // 从Map中提取最新的记录列表
                List<RealdataCache> filteredRealdataCaches = new ArrayList<>(latestRecordsByStation.values());
                for(RealdataCache realdataCache1: filteredRealdataCaches){
                    if(realdataCache1.getFlow() != null){
                        total+=realdataCache1.getFlow();
                    }
                }

            }

            if (!stationNoList1.isEmpty()){
                LambdaQueryWrapper<RealdataCache> queryWrapper22 = new LambdaQueryWrapper<>();
                queryWrapper22.ge(RealdataCache::getRecordTime,firstTime)
                        .le(RealdataCache::getRecordTime,firstTime.plusMinutes(1))
                        .in(RealdataCache::getStation,stationNoList1);
                List<RealdataCache> realdataCaches2 = realdataCacheMapper.selectList(queryWrapper22);

                // 创建一个Map来存储每个站台的最新记录
                Map<Integer, RealdataCache> latestRecordsByStation2 = new HashMap<>();
                for (RealdataCache dataCache2 : realdataCaches2) {
                    Integer stationNo = dataCache2.getStation();
                    if (!latestRecordsByStation2.containsKey(stationNo) ||
                            dataCache2.getRecordTime().isAfter(latestRecordsByStation2.get(stationNo).getRecordTime())) {
                        latestRecordsByStation2.put(stationNo, dataCache2);
                    }
                }

                // 从Map中提取最新的记录列表
                List<RealdataCache> filteredRealdataCaches2 = new ArrayList<>(latestRecordsByStation2.values());

                for(RealdataCache realdataCache2: filteredRealdataCaches2){
                    if(realdataCache2.getFlow() != null){
                        part+=realdataCache2.getFlow();
                    }
                }
            }

            map.put("时间",firstTime.toString());
            map.put("总表",total);
            map.put("分表",part);

            res.add(map);
            firstTime = firstTime.plusMinutes(1);
        }
        return res;
    }





    @Override
    public List<Map<String, Object>> getThreeFlowUpAndDown() {
        List<Integer> stationNoList = new ArrayList<>(java.util.Arrays.asList(331, 332, 350, 351,458));

        List<Map<String, Object>> res = new ArrayList<>();
        for(Integer stationNo:stationNoList){
            Map<String, Object> map = new HashMap<>();
            StationInfo stationInfo = stationInfoMapper.selectById(stationNo);
            RealdataCache realdataCache = realdataCacheMapper.selectRecentOneByStation(stationNo);

            if(realdataCache!=null){
                map.put("站点名",stationInfo.getStationName());
                if(realdataCache.getFlow()!= null) map.put("瞬时流量",String.format("%.3f",realdataCache.getFlow()));
                else map.put("瞬时流量",0);
                if(realdataCache.getTotal()!= null) map.put("总流量",String.format("%.3f",realdataCache.getTotal()));
                else map.put("总流量",0);
            }
            res.add(map);
        }
        return res;
    }

    /**
     * 智慧看板总累计流量、实时流量
     * @return
     */
    public List<Map<String,Object>> getOverFlow() {
        List<Map<String, Object>> res = new ArrayList<>();
        LambdaQueryWrapper<LineInfo> wrapper = new LambdaQueryWrapper<>();
        //屏蔽四通道表
        wrapper.ne(LineInfo::getLineNum,23);
        List<LineInfo> lineInfos = lineInfoMapper.selectList(wrapper);
        float allMain = 0f;

        float totalFlow = 0f;
        for (LineInfo lineInfo : lineInfos) {
            String lineName =  (lineInfo.getLineAlias()==null|| lineInfo.getLineAlias().equals("")) ?lineInfo.getLineName() :lineInfo.getLineAlias();
            //该路线的总表的和
            LambdaQueryWrapper<StationInfo> wp = new LambdaQueryWrapper<>();
            wp.eq(StationInfo::getUserType,0).eq(StationInfo::getLineId,lineInfo.getLineNum());
            List<StationInfo> mainStationInfoList = stationInfoMapper.selectList(wp);
            float mainTotal =0f;
            float flowTotal = 0f;
            for (StationInfo stationInfo : mainStationInfoList) {
                RealdataCache realdataCache = realdataCacheMapper.selectRecentOneByStation(stationInfo.getStationNo());
                if(realdataCache==null){
                    mainTotal+=0;
                    flowTotal+=0;
                    continue;
                }
                if(realdataCache.getFlow()!=null)  mainTotal+=realdataCache.getFlow();
                if(realdataCache.getTotal()!=null)  flowTotal+=realdataCache.getTotal();
            }

            allMain+=mainTotal;
            totalFlow+=flowTotal;

        }
        HashMap<String, Object> map3 = new LinkedHashMap<>();
        map3.put("园区总累计流量(t/h)",String.format("%.3f",totalFlow));
        map3.put("园区总实时流量(t/h)",String.format("%.3f",allMain));
        res.add(map3);

        return res;
    }

    /**
     * 根据站点名称获取电话号码
     * @param stationName
     * @return
     */
    @Override
    public String getTelByStationName(String stationName) {

        LambdaQueryWrapper<StationInfo> wrapper  = new LambdaQueryWrapper<>();
        wrapper.eq(StationInfo::getStationName,stationName);
        StationInfo stationInfo = this.stationInfoMapper.selectOne(wrapper);

        return stationInfo.getTel();
    }

    /**
     * 增加站点信息
     * @param stationInfo
     */
    @Override
    public void addStationInfo(StationInfo stationInfo) {
        LambdaQueryWrapper<StationInfo> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(StationInfo::getStationName,stationInfo.getStationName());
        StationInfo stationInfo1 = this.stationInfoMapper.selectOne(lambdaQueryWrapper);

        if(stationInfo1 != null){
            throw new MyException(MyExceptiontType.STATIONEXIST,null);
        }else{
            this.stationInfoMapper.insert(stationInfo);
        }
    }

    /**
     * 获取所有站点编号
     * @return
     */
    public List<Integer> getStationNoList(){
        List<Integer> stationNoList = new ArrayList<>();
        for(StationInfo stationInfo:this.baseMapper.selectList(null)){
            stationNoList.add(stationInfo.getStationNo());
        }
        return stationNoList;
    }

    /**
     * 查询用户所有站点名称
     * @param id
     * @return
     */
    @Override
    public List<Map<String,Object>> getStationNameList(Integer id) {
        User user = this.userMapper.selectById(id);
        List<Map<String,Object>> res = new ArrayList<>();

        if(user.getUserRole().equals(0)){
            //普通用户只能看到自己的站点
            LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(StationInfo::getUserId,id)
                    .eq(StationInfo::getIsUse,1);
            List<StationInfo> result = this.baseMapper.selectList(wrapper);
            for(StationInfo item:result){
                HashMap<String, Object> map = new HashMap<>();
                map.put("station_no",item.getStationNo());
                map.put("station_name",item.getStationName());
                res.add(map);
            }
        }else {
            QueryWrapper<StationInfo> wrapper = new QueryWrapper<>();
            wrapper.select("station_no,station_name")
                    .eq("is_use",1);
            List<StationInfo> station_list = stationInfoMapper.selectList(wrapper);
            for(StationInfo item : station_list){
                HashMap<String, Object> map = new HashMap<>();
                map.put("station_no",item.getStationNo());
                map.put("station_name",item.getStationName());
                res.add(map);
            }
        }
        return res;
    }

    /**
     * 查询用户线路分布情况
     * @return
     */
    @Override
    public Map<String, Integer> getUserLineDistribution() {
        Map<String,Integer> res = new LinkedHashMap<>();
        QueryWrapper<LineInfo> line_info = new QueryWrapper<>();
        line_info.select("line_num,line_name,line_alias");
        List<LineInfo> line_list = lineInfoMapper.selectList(line_info);

        for(LineInfo item : line_list){
            if(item.getLineNum()==23) continue;
            LambdaQueryWrapper<StationInfo> wp1 = new LambdaQueryWrapper<>();
            wp1.eq(StationInfo::getLineId,item.getLineNum()).eq(StationInfo::getIsUse,true);
            List<StationInfo> stationInfos = stationInfoMapper.selectList(wp1);
            List<Integer> ids = stationInfos.stream().map(StationInfo::getStationNo).collect(Collectors.toList());

            QueryWrapper<RealdataCache> wp2 = new QueryWrapper<>();
            wp2.select("distinct station").in("station",ids);
            Long onlineids = realdataCacheMapper.selectCount(wp2);

            String lineName;
            if(item.getLineAlias() ==null || item.getLineAlias().isEmpty()){
                lineName = item.getLineName();
            }else {
                lineName = item.getLineAlias();
            }
            res.put(lineName+"(有数据)",onlineids.intValue());
            res.put(lineName+"(无数据)",ids.size()- onlineids.intValue());

        }


        return res;
    }

    /**
     * 查询用户类别分布
     * @return
     */
    @Override
    public Map<String, Integer> getUserCategory() {
        LambdaQueryWrapper<StationInfo> major = new LambdaQueryWrapper<>();
        major.eq(StationInfo::getIsMaster,1);
        Integer major_num = this.baseMapper.selectCount(major).intValue();
        LambdaQueryWrapper<StationInfo> other = new LambdaQueryWrapper<>();
        other.eq(StationInfo::getIsMaster,0);
        Integer other_num = this.baseMapper.selectCount(other).intValue();
        Map<String,Integer> res = new HashMap<>();
        res.put("主要用户",major_num);
        res.put("普通用户",other_num);
        return res;
    }

    @Override
    public IPage<StationInfo> getAllStationInfo(Integer pageNo) {
        IPage<StationInfo> stationIPage = new Page<>(pageNo,5);
        QueryWrapper<StationInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByAsc("station_no");
        return stationInfoMapper.selectPage(stationIPage,queryWrapper);
    }

    /**
     *查找所有站点信息
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Override
    public IPage<StationInfo> getAllStationInfo(Integer pageNo,Integer pageSize) {
        IPage<StationInfo> stationIPage = new Page<>(pageNo,pageSize);
        QueryWrapper<StationInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByAsc("station_no");
        return stationInfoMapper.selectPage(stationIPage,queryWrapper);
    }
    @Override
    public List<StationInfo> getAllStationInfo(){
        QueryWrapper<StationInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByAsc("station_no");
        return stationInfoMapper.selectList(queryWrapper);
    }

    /**
     *查找所有站点信息
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Override
    public IPage<StationInfo> getStationInfoByXunjian(Integer pageNo,Integer pageSize) {
        IPage<StationInfo> stationIPage = new Page<>(pageNo,pageSize);
        QueryWrapper<StationInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByAsc("station_no");
        return stationInfoMapper.selectPage(stationIPage,queryWrapper);
    }
    /**
     * 修改站点启用状态
     * @param stationNo
     * @return
     */
    @Override
    public Result<Object> updateStationStatus(Integer stationNo) {

        LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(StationInfo::getStationNo,stationNo);
        StationInfo result = this.baseMapper.selectOne(wrapper);
        if (result == null) {
            throw new MyException(MyExceptiontType.STATIONNOTFIND,null);
        }

        if(result.getIsUse().equals(true)){
            result.setIsUse(false);
        } else if (result.getIsUse().equals(false)) {
            result.setIsUse(true);
        }
        stationInfoMapper.updateById(result);
        return Result.success("修改成功");
    }

    /**
     * 修改站点参数信息
     * @param stationParametersVo
     */
    @Override
    public void updateStationInfo(StationParametersVo stationParametersVo) {

        if(this.getById(stationParametersVo.getStationNo()) == null) {
            throw new MyException(MyExceptiontType.STATIONNOTFIND, null);
        }
        LambdaQueryWrapper<StationInfo> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(StationInfo::getStationName, stationParametersVo.getStationName());
        StationInfo stationInfo1 = this.stationInfoMapper.selectOne(lambdaQueryWrapper);

        if(stationInfo1 != null && !Objects.equals(stationInfo1.getStationNo(), stationParametersVo.getStationNo())){
            throw new MyException(MyExceptiontType.STATIONEXIST,null);
        }else{
            StationInfo stationInfo = new StationInfo();
            BeanUtils.copyProperties(stationParametersVo,stationInfo);
//            stationInfo.setStationNo(stationParameters.getStationNo());
//            stationInfo.setStationName(stationParameters.getStationName());
//            stationInfo.setUserId(stationParameters.getUserId());
//            stationInfo.setLineId(stationParameters.getLineId());
//            stationInfo.setUserType(stationParameters.getUserType());
//            stationInfo.setYbType(stationParameters.getYbType());
//            stationInfo.setAddr(stationParameters.getAddr());
//            stationInfo.setGprsId(stationParameters.getGprsId());
//            stationInfo.setTrange(stationParameters.getTrange());
//            stationInfo.setPrange(stationParameters.getPrange());
//            stationInfo.setFrange(stationParameters.getFrange());
//            stationInfo.setTel(stationParameters.getTel());
//            stationInfo.setComType(stationParameters.getComType());
//            stationInfo.setFupa(stationParameters.getFupa());
//            stationInfo.setFdowna(stationParameters.getFdowna());
//            stationInfo.setTupa(stationParameters.getTupa());
//            stationInfo.setTdowna(stationParameters.getTdowna());
//            stationInfo.setPupa(stationParameters.getPupa());
//            stationInfo.setPdowna(stationParameters.getPdowna());
//            stationInfo.setIsMaster(stationParameters.getIsMaster());
//            stationInfo.setMinConsume(stationParameters.getMinConsume());
//            stationInfo.setBalance(stationParameters.getBalance());
//            stationInfo.setBalanceAlarm(stationParameters.getBalanceAlarm());
//            stationInfo.setMaxOverdraft(stationParameters.getMaxOverdraft());
//            stationInfo.setSentTimeStart(stationParameters.getSentTimeStart());
//            stationInfo.setSentTimeEnd(stationParameters.getSentTimeEnd());
//            stationInfo.setComPassword(stationParameters.getComPassword());
//            stationInfo.setFreupa(stationParameters.getFreupa());
//            stationInfo.setFredowna(stationParameters.getFredowna());
//            stationInfo.setBankCardNumber(stationParameters.getBankCardNumber());
            stationInfoMapper.updateById(stationInfo);
        }

    }

    /**
     * 修改站点通讯参数信息
     * @param comParametersVo
     */
    @Override
    public void updateComParameters(ComParametersVo comParametersVo) {
        UpdateWrapper<StationInfo> updateWrapper = new UpdateWrapper<>();
        updateWrapper.set("com_ip", comParametersVo.getComIp());
        updateWrapper.set("com_port", comParametersVo.getComPort());
        updateWrapper.set("tcp_addr", comParametersVo.getTcpAddr());
        updateWrapper.set("udp_addr", comParametersVo.getUdpAddr());
        updateWrapper.set("account_hour", comParametersVo.getAccountHour());
        updateWrapper.set("account_date", comParametersVo.getAccountDate());
        updateWrapper.set("storage_time", comParametersVo.getStorageTime());
        updateWrapper.set("collection_circle", comParametersVo.getCollectionCircle());
        updateWrapper.set("storage_circle", comParametersVo.getStorageCircle());
        updateWrapper.set("instruct_circle", comParametersVo.getInstructCircle());
        stationInfoMapper.update(null,updateWrapper);
    }

    @Override
    public String getLineNameByStationNo(Integer station_no) {
        StationInfo res = stationInfoMapper.selectById(station_no);
        LambdaQueryWrapper<LineInfo> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(LineInfo::getLineNum,res.getLineId());
        LineInfo result = lineInfoMapper.selectOne(wrapper);
        if(result.getLineAlias().isEmpty() || result == null){
            return result.getLineName();
        }
        return result.getLineAlias();
    }

    /**
     * 根据日期返回当日流速排名
     * @param date
     * @return
     */
    @Override
    public Map<String, Float> getFlowRankListByDate(LocalDate date) {
        //根据日期查询小时表流速
        List<StationHour> list = this.flowHourMapper.selectStationHourList(date,date.plusDays(1));

        Map<String,Float> res = new HashMap<>();
        Map<String,Integer> cnt = new HashMap<>();

        //  求出 站点 流速和 存入res，站点 记录条数 存入cnt
        for(int i = 0;i < list.size(); i ++){
            StationHour stationInfo = list.get(i);
            if(stationInfo == null) {
                continue;
            }
            String stationName = stationInfo.getStationName();
            Float item = stationInfo.getFlowStart();
            if(item == null){
                item = 0F;
            }
          
            if(res.containsKey(stationName)){
                res.putIfAbsent(stationName, 0F);
                Float t = res.get(stationName);
                Integer tt = cnt.get(stationName);
                res.put(stationName, t + item);
                cnt.put(stationName, tt + 1);
            }else{
                res.put(stationName,item);
                cnt.put(stationName,1);
            }
        }

        //遍历res与cnt 将平均流速求出来存入result
        Map<String,Float> result = new HashMap<>();
        for (String key : res.keySet()) {
            result.put(key,res.get(key) / cnt.get(key));
        }

        //对result按value从大到小排序
        LinkedHashMap<String,Float> resultMap = result.entrySet().stream().sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (s, s2) -> s, LinkedHashMap::new));

        //取出排名前十的数据
        LinkedHashMap<String,Float> resultMap1 = new LinkedHashMap<>();
        int i = 0;
        for(String key : resultMap.keySet()){
            if(i >= 10){
                break;
            }
            i ++;
            resultMap1.put(key,resultMap.get(key));
        }
        return resultMap1;
    }

    /**
     * 根据日期返回当日平均温度排名
     * @param date
     * @return
     */
    @Override
    public Map<String, Float> getTempRankListByDate(LocalDate date) {
        //根据日期查询小时表温度
        List<StationHour> list = this.flowHourMapper.selectStationHourList(date,date.plusDays(1));

        Map<String,Float> res = new HashMap<>();
        Map<String,Integer> cnt = new HashMap<>();

        //  求出 站点 温度和 存入res，站点 记录条数 存入cnt
        for(int i = 0;i < list.size(); i ++){
            StationHour stationInfo = list.get(i);
            if(stationInfo == null) {
                continue;
            }
            String stationName = stationInfo.getStationName();
            Float item = stationInfo.getTempStart();
            if(item == null){
                item = 0F;
            }

            if(res.containsKey(stationName)){
                res.putIfAbsent(stationName, 0F);
                Float t = res.get(stationName);
                Integer tt = cnt.get(stationName);
                res.put(stationName, t + item);
                cnt.put(stationName, tt + 1);
            }else{
                res.put(stationName,item);
                cnt.put(stationName,1);
            }
        }

        //遍历res与cnt 将平均温度求出来存入result
        Map<String,Float> result = new HashMap<>();
        for (String key : res.keySet()) {
            result.put(key,res.get(key) / cnt.get(key));
        }

        //对result按value从大到小排序
        LinkedHashMap<String,Float> resultMap = result.entrySet().stream().sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (s, s2) -> s, LinkedHashMap::new));

        //取出排名前十的数据
        LinkedHashMap<String,Float> resultMap1 = new LinkedHashMap<>();
        int i = 0;
        for(String key : resultMap.keySet()){
            if(i >= 10) break;
            i ++;
            resultMap1.put(key,resultMap.get(key));
        }
        return resultMap1;
    }

    /**
     * 根据日期返回当日平均压力排名
     * @param date
     * @return
     */
    @Override
    public Map<String, Float> getPresRankListByDate(LocalDate date) {
        //根据日期查询小时表压力
        List<StationHour> list = this.flowHourMapper.selectStationHourList(date,date.plusDays(1));

        Map<String,Float> res = new HashMap<>();
        Map<String,Integer> cnt = new HashMap<>();

        //  求出 站点 压力和 存入res，站点 记录条数 存入cnt
        for(int i = 0;i < list.size(); i ++){
            StationHour stationInfo = list.get(i);
            if(stationInfo == null) {
                continue;
            }
            String stationName = stationInfo.getStationName();
            Float item = stationInfo.getPresStart();
            if(item == null){
                item = 0F;
            }

            if(res.containsKey(stationName)){
                res.putIfAbsent(stationName, 0F);
                Float t = res.get(stationName);
                Integer tt = cnt.get(stationName);
                res.put(stationName, t + item);
                cnt.put(stationName, tt + 1);
            }else{
                res.put(stationName,item);
                cnt.put(stationName,1);
            }
        }

        //遍历res与cnt 将平均压力求出来存入result
        Map<String,Float> result = new HashMap<>();
        for (String key : res.keySet()) {
            result.put(key,res.get(key) / cnt.get(key));
        }

        //对result按value从大到小排序
        LinkedHashMap<String,Float> resultMap = result.entrySet().stream().sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue, (s, s2) -> s, LinkedHashMap::new));

        //取出排名前十的数据
        LinkedHashMap<String,Float> resultMap1 = new LinkedHashMap<>();
        int i = 0;
        for(String key : resultMap.keySet()){
            if(i >= 10) break;
            i ++;
            resultMap1.put(key,resultMap.get(key));
        }
        return resultMap1;
    }
    @Override
    public List<StationInfo> getAllUsedStationInfo(){
        QueryWrapper<StationInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("is_use",true).orderByAsc("station_no");
        return stationInfoMapper.selectList(queryWrapper);
    }
    /**
     * 设置报表结算的小时和日期
     * @param id
     * @param account_date
     * @param account_hour
     */
    @Override
    public void updateAccountDateAndHour(Integer id,Integer account_date, Integer account_hour) {
        StationInfo stationInfo = new StationInfo();
        stationInfo.setStationNo(id);
        stationInfo.setAccountDate(account_date);
        stationInfo.setAccountHour(account_hour);
        int result =stationInfoMapper.updateById(stationInfo);
        if(result!=1){
            throw new MyException(MyExceptiontType.UPDATEERROR,null);
        }
    }

    @Override
    public Integer getAccountHourByStation(Integer station_no) {
        LambdaQueryWrapper<StationInfo> w = new LambdaQueryWrapper<>();
        StationInfo stationInfo = this.baseMapper.selectById(station_no);
        return stationInfo.getAccountHour();
    }

    @Override
    public Integer getAccountDateByStation(Integer station_no) {
        LambdaQueryWrapper<StationInfo> w = new LambdaQueryWrapper<>();
        StationInfo stationInfo = this.baseMapper.selectById(station_no);
        return stationInfo.getAccountDate();
    }
    /**
     * 根据站点编号查询站点名称
     * @param station_no
     * @return station_name
     */
    public String getStationNameByStationNo(Integer station_no){
        LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(StationInfo::getStationNo,station_no);
        try {
            return stationInfoMapper.selectOne(wrapper).getStationName();
        }catch (Exception e){
            return null;
        }

    }
    @Override
    public List<String> getStationNoNameListByLikeName(String likeName) {
        if(likeName == null){
            likeName = "";
        }
        LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
        wrapper.like(StationInfo::getStationName,likeName);
//                .eq(StationInfo::getIsUse,1);
        List<StationInfo> stationInfoList = stationInfoMapper.selectList(wrapper);

        ArrayList<String> res = new ArrayList<>();
        for (StationInfo stationInfo : stationInfoList) {
            res.add(stationInfo.getStationNo()+" "+stationInfo.getStationName());

        }
        return res;
    }

    @Override
    public List<StationInfo> getAllUsedStation() {
        LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(StationInfo::getIsUse,1);
        return stationInfoMapper.selectList(wrapper);
    }

    /**
     * 查询所有非叶子节点
     * @return
     */
    @Override
    public List<StationInfo> getAllLineStation() {
        LambdaQueryWrapper<StationInfo> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(StationInfo::getIsLeaf,0);
        return stationInfoMapper.selectList(wrapper);
    }

    /**
     * 所有站点信息表，导出到Excel
     * @param response
     * @throws IOException
     */
    @Override
    public void getAllStationExcel(HttpServletResponse response) throws IOException {

        List<StationInfo> resList = getAllStationInfo();

        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("站点信息表", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(ExcelStyleUtils.getHeadStyle(), ExcelStyleUtils.getContentStyle());

            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), StationInfo.class)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .autoCloseStream(Boolean.FALSE)
                    .sheet("sheet1")
                    .doWrite(resList);

        } catch (Exception e) {
            // 重置response
//            throw  e;
//            response.reset();
//            response.setContentType("application/json");
//            response.setCharacterEncoding("utf-8");
//            Map<String, String> map = MapUtils.newHashMap();
//            map.put("status", "failure");
//
//            map.put("message", "下载文件失败" + e.getMessage());
//            response.getWriter().println(JSONUtils.toJSONString(map));
//            throw new MyException(MyExceptiontType.FILEDOWNLOADFAIL,"下载文件失败");
            log.error("生成站点信息 Excel 文件失败", e);
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR); // 设置错误状态码
            throw new MyException(MyExceptiontType.FILEDOWNLOADFAIL,"下载文件失败: " + e.getMessage());
        }
    }

    /**
     * 预测未来三天的数据走向，取平均
     * @param station_no
     * @param todayStart
     * @param threeDaysAgoStart
     * @return
     */
    @Override
    public Map<Integer,DayDataVo> getFutureData(Integer station_no, LocalDateTime todayStart, LocalDateTime threeDaysAgoStart) {
        //1、从数据库里查找3天的数据
        LambdaQueryWrapper<FlowHour> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(FlowHour::getStation,station_no)
                .between(FlowHour::getRecStart, threeDaysAgoStart.plusMinutes(10), todayStart.minusMinutes(10))
                .orderByAsc(FlowHour::getRecStart);
        List<FlowHour> flowHourList = flowHourMapper.selectList(wrapper);

        return predictData(flowHourList,todayStart);
    }

    /**
     * 导出预测数据到excel
     * @param stationNo
     * @param todayStart
     * @param threeDaysAgoStart
     */
    @Override
    public void getFutureData2Excel(Integer stationNo, LocalDateTime todayStart, LocalDateTime threeDaysAgoStart,HttpServletResponse response){
        Map<Integer, DayDataVo> resMap = getFutureData(stationNo,todayStart,threeDaysAgoStart);

        // 准备工作，创建别名的映射关系
        ExcelWriter wr = ExcelUtil.getWriter( true);
        wr.addHeaderAlias("hour", "时间");
        wr.addHeaderAlias("flow", "流量");
        wr.addHeaderAlias("temperature", "温度");
        wr.addHeaderAlias("pressure", "压力");
        wr.addHeaderAlias("frequency", "频率");
        wr.getWorkbook().removeSheetAt(0);//移除默认sheet

        int day = 1;
        for (DayDataVo dv : resMap.values()){
            wr.setSheet("第"+(day++)+"天");

            List<HourDataVo> hourDataVoList = new ArrayList<>();
            for (int i=0;i<24;i++){
                HourDataVo hv = new HourDataVo(i);
                hv.setFlow(dv.getFlow()[i]);
                hv.setTemperature(dv.getTemperature()[i]);
                hv.setPressure(dv.getPressure()[i]);
                hv.setFrequency(dv.getFrequency()[i]);
                hourDataVoList.add(hv);
            }

            // 写入当前Sheet数据
            wr.write(hourDataVoList, true);
        }

//        PoiExcelStyle.setStyleSet(wr,hourDataVoList);
        DateTimeFormatter formatter1 = DateTimeFormatter.ofPattern("yyyy年M月d日 ");
        String formattedDate = todayStart.format(formatter1);

        try {
            String fileName = URLEncoder.encode(formattedDate +"数据预测表", "UTF-8").replaceAll("\\+", "%20");

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader( "Content-Disposition", "attachment;filename="+fileName+".xlsx");

            ServletOutputStream out = response.getOutputStream();
            wr.flush(out,true);
        }catch (IOException e){
            log.error("数据预测excel导出错误",e);
        }finally {
            wr.close();
            IoUtil.close(System.out);
        }
    }

    private Map<Integer, DayDataVo> predictData(List<FlowHour> flowHourList,LocalDateTime todayStart){
        //1、按天分好，再按小时分好，存在map里
        Map<Integer,DayDataVo> dayDataMap = MapUtils.newHashMap();

        int i=1;
        for (FlowHour flowHour : flowHourList) {
            int day = flowHour.getRecStart().getDayOfMonth();
            if ( !dayDataMap.containsKey(day) ){
                dayDataMap.putIfAbsent(day,new DayDataVo(flowHour.getRecStart()));
                dayDataMap.get(day).setDate(todayStart.plusDays(i++));
            }

            int hour = flowHour.getRecStart().getHour();
            dayDataMap.get(day).setFlow(hour,flowHour.getFlowStart());
            dayDataMap.get(day).setPressure(hour,flowHour.getPresStart());
            dayDataMap.get(day).setTemperature(hour,flowHour.getTempStart());
            dayDataMap.get(day).setFrequency(hour,flowHour.getFreqStart());
        }

        //2、开始预测
        DayDataVo dayFuture = performPrediction(dayDataMap);
        //3、填回再预测
        Set<Integer> keys = dayDataMap.keySet();
        for (Integer key : keys) {
            dayFuture.setDate(dayDataMap.get(key).getDate());
            dayDataMap.put(key,dayFuture);
            dayFuture = performPrediction(dayDataMap);
        }

        return dayDataMap;
    }

    private DayDataVo performPrediction(Map<Integer, DayDataVo> dayDataMap) {
        DayDataVo newDayData = new DayDataVo();
        float[] nflow = new float[24];
        float[] npress = new float[24];
        float[] ntmp = new float[24];
        float[] nfreq = new float[24];

        //三天的数据加到一天上
        for (DayDataVo dayData : dayDataMap.values()) {
            for (int i = 0; i < 24; i++) {
                nflow[i]+=dayData.getFlow()[i];
                npress[i]+=dayData.getPressure()[i];
                ntmp[i]+=dayData.getTemperature()[i];
                nfreq[i]+=dayData.getFrequency()[i];
            }
        }

        //取平均
        for (int i = 0; i < 24; i++) {
            nflow[i]=nflow[i]/3;
            npress[i]=npress[i]/3;
            ntmp[i]=ntmp[i]/3;
            nfreq[i]=nfreq[i]/3;
        }

        newDayData.setFlow(nflow);
        newDayData.setPressure(npress);
        newDayData.setTemperature(ntmp);
        newDayData.setFrequency(nfreq);

        return newDayData;
    }

}
